﻿using System;
using System.Data;
using System.Data.Common;
using System.Text;
using Besture.Common;
using Besture.Model;

namespace Besture.Dal
{
    public partial class AppraisalDal : BaseDataAccess
    {


        public int GetDataTableByHotelsAndUserId(int iHotelsId, int iUserId)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append(string.Format("select * from TBAppraisal where UserID={0} and HotelsID={1} ",iUserId,iHotelsId));
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count>0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception exception)
            {
                return 0;
            }
        }

        public DataTable GetDataTableByUserId(int iUserId)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append(" SELECT tbh.HotelsName,tbub.NickName, " +
                                 " (select Name from TBDictionaryType where Type=2 and Code=tba.CheckStatus) CheckStatusName, " +
                                 " tba.* FROM TBAppraisal tba left join TBHotels tbh on tba.HotelsID = tbh.ID " +
                                 " left join TBUserBase tbub on tba.UserID = tbub.ID where tba.UserID=" + iUserId + " order by tba.UpdateTime desc ");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                return dt;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 获取详细信息
        /// </summary>
        /// <param name="id">主键值</param>
        /// <returns></returns>
        public DataTable GetAppraisalByHotID(int hotId)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append("SELECT " +
                                 " (select LoginName from TBUserBase where ID= tbrr.UserID)  LoginName," +
                                 " tbrr.* FROM TBReviewsReply tbrr  " +
                                 " join TBAppraisal tba on tbrr.ReviewsID = tba.ID " +
                                 " where tba.HotelsID=@HotelsID ");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                base.SetParameter(ldcDBCommand, "@HotelsID", DbType.Int32, hotId);
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0)
                    return dt;
                else return null;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 获取详细信息
        /// </summary>
        /// <param name="id">主键值</param>
        /// <returns></returns>
        public AppraisalModel GetModel(int id)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append("SELECT * FROM TBAppraisal");
                selectSql.Append(" WHERE ");
                selectSql.Append(" ID=@ID ");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.Int32, id);
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0)
                    return ModelConvertHelper<AppraisalModel>.ConvertToModelList(dt)[0];
                else return null;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 获取全部数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable()
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append(" SELECT tbh.HotelsName,tbub.NickName, " +
                                 " (select Name from TBDictionaryType where Type=2 and Code=tba.CheckStatus) CheckStatusName, " +
                                 " tba.* FROM TBAppraisal tba left join TBHotels tbh on tba.HotelsID = tbh.ID " +
                                 " left join TBUserBase tbub on tba.UserID = tbub.ID order by tba.UpdateTime desc ");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                return dt;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(AppraisalModel model)
        {
            var identityId = -1;
            try
            {

				
            string sqlString = string.Format(@"
                INSERT INTO TBAppraisal
                (
                        HotelsID
                        ,UserID
                        ,Content
                        ,AppraisalTime
                        ,Location
                        ,Facilities
                        ,Service
                        ,Health
                        ,CheckStatus
                        ,SortID
                        ,IsDeleted
                        ,Created
                        ,CreateTime
                        ,Updated
                        ,UpdateTime
                )
                VALUES
                (
                        @HotelsID
                       ,@UserID
                       ,@Content
                       ,@AppraisalTime
                       ,@Location
                       ,@Facilities
                       ,@Service
                       ,@Health
                       ,@CheckStatus
                       ,@SortID
                       ,@IsDeleted
                       ,@Created
                       ,@CreateTime
                       ,@Updated
                       ,@UpdateTime
                )SELECT @@IDENTITY");

            DbCommand ldcDBCommand = base.GetCommand(sqlString);

            //参数设定
            SetParameter(ldcDBCommand, "@HotelsID", DbType.Int32, model.HotelsID);
            SetParameter(ldcDBCommand, "@UserID", DbType.Int32, model.UserID);
            SetParameter(ldcDBCommand, "@Content", DbType.String, model.Content);
            SetParameter(ldcDBCommand, "@AppraisalTime", DbType.DateTime, model.AppraisalTime);
            SetParameter(ldcDBCommand, "@Location", DbType.Int32, model.Location);
            SetParameter(ldcDBCommand, "@Facilities", DbType.Int32, model.Facilities);
            SetParameter(ldcDBCommand, "@Service", DbType.Int32, model.Service);
            SetParameter(ldcDBCommand, "@Health", DbType.Int32, model.Health);
            SetParameter(ldcDBCommand, "@CheckStatus", DbType.Int32, model.CheckStatus);
            SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);
            SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
            SetParameter(ldcDBCommand, "@Created", DbType.Int32, model.Created);
            SetParameter(ldcDBCommand, "@CreateTime", DbType.DateTime, model.CreateTime);
            SetParameter(ldcDBCommand, "@Updated", DbType.Int32, model.Updated);
            SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);



                identityId = base.ExecuteScalarInsert(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return identityId;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(AppraisalModel model)
        {
            int iTmp = -1;
            try
            {
				
            string sqlString = string.Format(@"
                UPDATE TBAppraisal
                SET
                    HotelsID = @HotelsID
                    ,UserID = @UserID
                    ,Content = @Content
                    ,AppraisalTime = @AppraisalTime
                    ,Location = @Location
                    ,Facilities = @Facilities
                    ,Service = @Service
                    ,Health = @Health
                    ,CheckStatus = @CheckStatus
                    ,SortID = @SortID
                    ,IsDeleted = @IsDeleted
                    ,Created = @Created
                    ,Updated = @Updated
                    ,UpdateTime = @UpdateTime
                WHERE
                    ID = @ID
            ");

            DbCommand ldcDBCommand = base.GetCommand(sqlString);
            //绑定SQL参数
            SetParameter(ldcDBCommand, "@ID", DbType.Int32, model.ID);
            SetParameter(ldcDBCommand, "@HotelsID", DbType.Int32, model.HotelsID);
            SetParameter(ldcDBCommand, "@UserID", DbType.Int32, model.UserID);
            SetParameter(ldcDBCommand, "@Content", DbType.String, model.Content);
            SetParameter(ldcDBCommand, "@AppraisalTime", DbType.DateTime, model.AppraisalTime);
            SetParameter(ldcDBCommand, "@Location", DbType.Int32, model.Location);
            SetParameter(ldcDBCommand, "@Facilities", DbType.Int32, model.Facilities);
            SetParameter(ldcDBCommand, "@Service", DbType.Int32, model.Service);
            SetParameter(ldcDBCommand, "@Health", DbType.Int32, model.Health);
            SetParameter(ldcDBCommand, "@CheckStatus", DbType.Int32, model.CheckStatus);
            SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);
            SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
            SetParameter(ldcDBCommand, "@Created", DbType.Int32, model.Created);
            SetParameter(ldcDBCommand, "@Updated", DbType.Int32, model.Updated);
            SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);


                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }

        /// <summary>
        /// 单个删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(int id)
        {
            int iTmp = -1;
            try
            {
                StringBuilder updateSql = new StringBuilder();
                updateSql.Append("DELETE FROM TBAppraisal");
                updateSql.Append(" WHERE ");
                updateSql.Append(" ID=@ID ");
                DbCommand ldcDBCommand = base.GetCommand(updateSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.Int32, id);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="idList"></param>
        /// <returns></returns>
        public int DeleteList(string ids)
        {
            int iTmp = -1;
            try
            {
                StringBuilder updateSql = new StringBuilder();
                updateSql.Append("exec('DELETE FROM TBAppraisal");
                updateSql.Append(" WHERE ");
                updateSql.Append(" ID in ('+@ID+') ')");
                DbCommand ldcDBCommand = base.GetCommand(updateSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.String, ids);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }



        public int Update(string iID, int iService)
        {
            int iTmp = -1;
            try
            {
                string sqlString = string.Format(@"UPDATE TBAppraisal SET CheckStatus =" + iService + " WHERE ID in (" + iID + ") ");
                DbCommand ldcDBCommand = base.GetCommand(sqlString);  
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }

    }
}

